/**
 * Copyright � 2012 GestureSoft. ALL RIGHTS RESERVED.
 * This file contains proprietary and GestureSoft CONFIDENTIAL Information.
 * Use, disclosure or reproduction is prohibited.
 * 
 * @fileName DataBaseManager.as
 * @creationDate Aug 8, 2012
 * @author 
 * @version 1.0
 */
package com.gesturesoft.imultimax.model
{
	
	import com.gesturesoft.imultimax.AppConst;
	import com.gesturesoft.imultimax.model.error.DataBaseError;
	import com.gesturesoft.imultimax.model.interfaces.IDatabaseManager;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.filesystem.File;
	
	import mx.collections.ArrayCollection;

	public class DataBaseManager implements IDatabaseManager
	{
		
		private var _sqlConnection : SQLConnection;
		
		public function DataBaseManager()
		{
			
			_sqlConnection = new SQLConnection();
			_sqlConnection.open(File.applicationStorageDirectory.resolvePath("iMultimax.db"));
			
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = _sqlConnection;
			
		}
		
		/**
		 * Create table if not exist
		 * @param tableName name of the table to create
		 * 
		 */ 
		
		public function prepareTable(tableName:String):void
		{
			
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = _sqlConnection;
			
			//If not exist create table for entering version details
			switch(tableName)
			{
				
				case "companies":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS companies (" +
						"version STRING, " + 
						"appname STRING, " +
						"server STRING, " +
						"configFile STRING, " +
						"is_enable STRING, " +
						"startup STRING, " +
						"asset_version STRING, " +
						"style_version STRING, " +
						"currency STRING" +
						")";
					break;
					
				}
				
				case "style_main":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS style_main (" +
						"asset_version STRING, " + 
						"style_version STRING, " +
						"company STRING )";
					break;
					
				}
					
				case "assets":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS assets (" +
						"name STRING, " + 
						"type STRING, " +
						"value STRING, "+
						"company STRING )";
					break;
					
				}
					
				case "styles":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS styles (" +
						"class_name STRING, " + 
						"property STRING, " +
						"value STRING, "+
 						"company STRING )";
					break;
					
				}
					
				case "currencies":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS currencies (" +
						"label STRING, " + 
						"country STRING)";
					break;
					
				}
					
				case "user":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS user (pin_number STRING, email STRING, phone_number STRING, activation_code STRING, status STRING, attempt STRING, locked STRING)";
						
					break;
					
				}	
					
				case "beforelist":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS beforelist (name STRING, contact STRING, amount STRING, type STRING, date TEXT DEFAULT (strftime('%d-%m-%Y %H:%M:%S','now', 'localtime')))";
					
					break;
					
				}
					
				case "savedforlater":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS savedforlater (name STRING, contact STRING, amount STRING, status STRING, type STRING, date TEXT DEFAULT (strftime('%d-%m-%Y %H:%M:%S','now', 'localtime')))";
					
					break;
					
				}
					
				case "locationtable":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS locationtable (longitude STRING, latitude STRING, distance STRING, date TEXT DEFAULT (strftime('%d-%m-%Y %H:%M:%S','now', 'localtime')))";
					
					break;
					
				}
					
				case "networkinfotable":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS networkinfotable (interfaceName STRING, mTU STRING, display_Name_Interface STRING, active STRING, hardware_Address STRING, address STRING, broadcast_address STRING, ipversion STRING, prefixlength STRING)";
					
					break;
					
				}
					
				case "androidnetworkinfotable":
				{
					
					stmt.text = 
						"CREATE TABLE IF NOT EXISTS androidnetworkinfotable (interfaceName STRING, mTU STRING, display_Name_Interface STRING, active STRING, hardware_Address STRING, address STRING, broadcast_address STRING, ipversion STRING, prefixlength STRING)";
					
					break;
					
				}
					
				default:
				{
					
					break;
					
				}
					
			}
			
			stmt.execute();
			
		}
		
		/**
		 * @param queryString query to execute
		 * @Return query result as array collection
		 * 
		 */ 
		
		public function getData(queryString:String):ArrayCollection
		{
			
			var result:ArrayCollection;
			
			try
			{
				
				var stmt:SQLStatement = new SQLStatement();
				stmt.sqlConnection = this._sqlConnection;
				stmt.text = queryString;
				stmt.execute();
				
				result = new ArrayCollection(stmt.getResult().data);
				
			} 
			catch(error:Error) 
			{
				
				throw new DataBaseError(AppConst.DATABASE_DATA_RETRIEVE_ERROR, AppConst.DATABASE_DATA_RETRIEVE_ERROR_CODE);
				
			}
			
			return result;
			
		}
		
		/**
		 * 
		 * Insert value to database
		 * @param queryString query to execute
		 */
		
		public function insertValues(queryString:String):void
		{
			
			try
			{
				
				var stmt:SQLStatement = new SQLStatement();
				stmt.sqlConnection = this._sqlConnection;
				stmt.text =	queryString;
				stmt.execute();
				
			} 
			catch(error:Error) 
			{
				
				throw new DataBaseError(AppConst.DATABASE_INSERTION_ERROR, AppConst.DATABASE_INSERTION_ERROR_CODE);
				
			}
			
		}
		
		/**
		 * Update table values
		 * @param queryString query to execute
		 */ 
		public function update(queryString:String):void
		{
			
			try
			{
				
				var stmt:SQLStatement = new SQLStatement();
				stmt.sqlConnection = this._sqlConnection;
				stmt.text =	queryString;
				stmt.execute();
				
			} 
			catch(error:Error) 
			{
				
				throw new DataBaseError(AppConst.DATABASE_UPDATION_ERROR, AppConst.DATABASE_UPDATION_ERROR_CODE);
				
			}
			
		}
		
		/**
		 * execute the query
		 */ 
		
		public function execute(query:String):void
		{
			
			try
			{
				
				var stmt:SQLStatement = new SQLStatement();
				stmt.sqlConnection = this._sqlConnection;
				stmt.text =	query;
				stmt.execute();
				
			} 
			catch(error:Error) 
			{
				
				throw new DataBaseError(AppConst.DATABASE_UPDATION_ERROR, AppConst.DATABASE_UPDATION_ERROR_CODE);
				
			}
			
		}

		
		/**
		 * 
		 * @param Return the number of rows
		 */ 
		
		public function getCount(queryString:String):SQLResult
		{
			
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = this._sqlConnection;
			stmt.text =	queryString;
			var count:SQLResult = stmt.getResult();
			return count;
			
		}
		
		/**
		 * Check whether the table is empty
		 * @param tablename table for empty check
		 * @Return true if table is empty
		 */ 
		
		public function isEmpty(tableName:String):Boolean
		{
			
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = this._sqlConnection;
			stmt.text =	"select * from "+tableName;
			stmt.execute();
			
			return (ArrayCollection(stmt.getResult().data).length == 0)?true:false;
			
		}
		
		/**
		 * @param query query to execute
		 * @Return array collection of rows satisfy the query
		 */ 
		
		public function getRow(query:String):ArrayCollection
		{
			
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = this._sqlConnection;
			stmt.text =	query;
			stmt.execute();
			
			return ArrayCollection(stmt.getResult().data);
			
		}
		
		/**
		 * Set start point for rollback functionality
		 * ie:if any error occure db rollback to the safe start point
		 */ 
		
		public function setTransactionStartPoint():void
		{
			
			_sqlConnection.begin();
			
		}
		
		public function commitTransaction():void
		{
			
			_sqlConnection.commit();
			
		}
		
		/**
		 * Roll back all the transactions if any error occur
		 */ 
		
		public function rollBackTransactions():void
		{
			
			this._sqlConnection.rollback();
			
		}
				
	}
	
}